package com.gopay.checkorder.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import com.gopay.checkorder.dao.BisBankOrderQueryDAO;
import com.gopay.common.base.dao.impl.BaseReadDAOImpl;
import com.gopay.common.constants.autosettle.SettlementExchangeConstants;
import com.gopay.common.constants.bank.BankOrderStatus;
import com.gopay.common.constants.bank.BankOrderType;
import com.gopay.common.constants.bank.BisBankBusinessProd;
import com.gopay.common.constants.bank.BisBankInterfaceChannel;
import com.gopay.common.constants.trans.TxnStaCode;
import com.gopay.common.domain.bank.BankOrder;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.util.DateUtils;


/**
 * @BisBankOrderQueryDAOImpl.java
 * @author fanghw
 * @2015年9月28日 下午5:10:27  www.gopay.com.cn Inc.All rights reserved.
 */
@Repository("bisBankOrderQueryDAO")
public class BisBankOrderQueryDAOImpl extends BaseReadDAOImpl<BankOrder, String>  implements BisBankOrderQueryDAO {

  
    
    private String buildBeginEndTimeClause(){
        Calendar calendar = Calendar.getInstance();
        
        //开始时间--最近一天
        calendar.setTime(new Date());
        calendar.add(Calendar.DAY_OF_MONTH, -1);
        String beginTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        
        //截至时间--90秒前
        calendar.setTime(new Date());
        calendar.add(Calendar.SECOND, -90);
        String endTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        
        StringBuilder sb=new StringBuilder(" and sendBankTm <to_date('"+endTime+"','yyyyMMddHH24miss')");
        sb.append(" and sendBankTm >to_date('"+beginTime+"','yyyyMMddHH24miss')");
        
        
        return sb.toString();
    }
    
    @Override
    public int getCheckOrderCount(String orderStatus) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrderCount();
        }
        StringBuffer hql = new StringBuffer("");
        hql.append(" select count(*) from BankOrder ");
        hql.append(" where orderStatus = :orderStatus and totalCheckTimes<7 ");        
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC') ");
        hql.append(" and orderType = :orderType ");  
        hql.append(buildBeginEndTimeClause());
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);  
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
       
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
    }

    @Override
    public List<BankOrder> getCheckOrder(String orderStatus, int pageIndex, int pageSize) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrder( pageIndex,  pageSize);
        }
        
        /*
        StringBuffer hql = new StringBuffer("");
        hql.append(" from BankOrder ");
        hql.append(" where orderStatus = :orderStatus and totalCheckTimes <7 ");
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC') ");
        hql.append(" and orderType = :orderType ");       
        hql.append(buildBeginEndTimeClause());
        hql.append(" order by orderId");
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
       
        
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
        
        */
        
        ////////SQL
        StringBuilder sql = buildQueryReturnFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");        
        sql.append(" and t.TOTAL_CHECK_TIMES <7 ");
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE != :bankBizProdCode ");
        sql.append(" and t.GOPAY_BANK_CODE not in ('ICBC','BOC','CCB','ABC') ");
        sql.append(buildBeginEndTimeClauseOfSQL());
        sql.append(" order by t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);        
       
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
    }
    
    @Override
    public int getCheckOrderCount(String orderStatus, int count) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrderCount();
        }
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder ");
        hql.append("where orderStatus = :orderStatus and totalCheckTimes = :totalCheckTimes ");
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC') ");        
        hql.append("and orderType = :orderType ");    
        hql.append(buildBeginEndTimeClause());
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("totalCheckTimes", count);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
            
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
       
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
        
       
        
    }
    
    @Override
    public List<BankOrder> getCheckOrder(String orderStatus, int count, int pageIndex, int pageSize) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrder(pageIndex,  pageSize);
        }
        /*
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus = :orderStatus and totalCheckTimes = :totalCheckTimes ");
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC') ");
        hql.append(" and orderType = :orderType ");       
        hql.append(buildBeginEndTimeClause());
        hql.append("order by orderId");
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("totalCheckTimes", count);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
      
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
       
        
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
        
        */
        
        ////////SQL
        StringBuilder sql = buildQueryReturnFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");        
        sql.append(" and t.TOTAL_CHECK_TIMES = :totalCheckTimes ");
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE !=:bankBizProdCode");
        // 交通银行凌晨跑批,0点-3点半不进行对账
        Calendar c = Calendar.getInstance();
        int hour = c.get(Calendar.HOUR_OF_DAY); // 24小时
        int minute = c.get(Calendar.MINUTE); // 分钟
        if (hour >= 0 && hour < 3) {
            sql.append(" and t.GOPAY_BANK_CODE not in ('ICBC','BOC','CCB','ABC','BOCOM','CMB','ALIPAY','WECHAT') ");
        } else if (hour == 3 && minute < 30) {
            sql.append(" and t.GOPAY_BANK_CODE not in ('ICBC','BOC','CCB','ABC','BOCOM','CMB','ALIPAY','WECHAT') ");
        } else {
            sql.append(" and t.GOPAY_BANK_CODE not in ('ICBC','BOC','CCB','ABC','CMB','ALIPAY','WECHAT') ");
        }
        
        sql.append(buildBeginEndTimeClauseOfSQL());
        sql.append(" order by t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("totalCheckTimes", count);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
        
    }
    
    private int getBankCallSuCheckOrderCount() {
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(bis.orderId) from BankOrder bis,CpsGenMainOrder cps ");
        hql.append("where bis.orderId = cps.gopayOrderId and bis.orderStatus = :orderStatus  ");
        hql.append(" and cps.txnStaCd!=:txnStaCd ");
        hql.append(" and bis.bankBizProdCode != :bankBizProdCode ");

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", BankOrderStatus.BANK_CALL_SUCCESS.toString());
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("txnStaCd", TxnStaCode.TXN_STA_CD_20000.value);
        
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
    }
    
    private List<BankOrder> getBankCallSuCheckOrder(int pageIndex,
            int pageSize) {
        StringBuffer hql = new StringBuffer("");
        Map<String, Object> params = new HashMap<String, Object>();
        hql.append("select bis from BankOrder bis,CpsGenMainOrder cps ");
        hql.append(" where bis.orderId = cps.gopayOrderId and bis.orderStatus = :orderStatus ");
        hql.append(" and bis.bankBizProdCode != :bankBizProdCode and cps.txnStaCd!=:txnStaCd");
        hql.append(" order by bis.orderId");
        
        params.put("orderStatus", BankOrderStatus.BANK_CALL_SUCCESS.toString());
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("txnStaCd", TxnStaCode.TXN_STA_CD_20000.value);
        
        List<BankOrder> list = findByHql(hql.toString(), params, pageIndex, pageSize);
        return list;
    }
    
    @Override
    public int getCheckOrderCountByBank(String orderStatus,String bankCode) {
        StringBuffer hql = new StringBuffer("");
        hql.append(" select count(*) from BankOrder ");
        hql.append(" where orderStatus = :orderStatus  and bankCode=:bankCode  and totalCheckTimes <7");
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        hql.append(" and orderType = :orderType ");   
        hql.append(buildBeginEndTimeClause());
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("bankCode", bankCode);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);

        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
    }
    
    @Override
    public List<BankOrder> getCheckOrderByBank(String orderStatus, int pageIndex, int pageSize,String bankCode) {
       /*
        StringBuffer hql = new StringBuffer("");
        hql.append(" from BankOrder ");
        hql.append(" where orderStatus = :orderStatus   ");
        hql.append(" and bankBizProdCode != :bankBizProdCode and bankCode=:bankCode and totalCheckTimes<7");
        hql.append(" and orderType = :orderType ");   
        hql.append(buildBeginEndTimeClause());
        hql.append(" order by totalCheckTimes,orderId");

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("bankCode", bankCode);
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);
        
        return list;
        */
        
        
        ////////SQL
        StringBuilder sql = buildQueryReturnFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");        
        sql.append(" and t.TOTAL_CHECK_TIMES <7 ");
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE != :bankBizProdCode ");
        sql.append(" and t.GOPAY_BANK_CODE = :bankCode ");
        sql.append(buildBeginEndTimeClauseOfSQL());
        sql.append(" order by t.TOTAL_CHECK_TIMES asc,t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);        
        params.put("bankCode", bankCode);
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
        
    }
    
    
    
    
    @Override
    public int getCheckOrderCount(Date date) {
        date =getNextDay(date);
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder ");
        hql.append("where orderStatus = :orderStatus ");
        hql.append(" and sendBankTm >= :date and totalCheckTimes >= :totalCheckTimes "); 
        hql.append(" and totalCheckTimes <12 "); 
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC') ");
        
        Map<String, Object> params = new HashMap<String, Object>();
        String status =BankOrderStatus.PROCESS;
        params.put("orderStatus", status);
        params.put("totalCheckTimes", 7);
        params.put("date", date);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
    }
    
    @Override
    public List<BankOrder> getCheckOrder(Date date, int pageIndex, int pageSize) {
        date =getNextDay(date);
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus =:orderStatus ");
        hql.append(" and sendBankTm >= :date ");    
        hql.append(" and bankBizProdCode != :bankBizProdCode and totalCheckTimes >= :totalCheckTimes ");
        hql.append(" and totalCheckTimes <12 "); 
        // 交通银行凌晨跑批,0点-3点半不进行对账
        Calendar c = Calendar.getInstance();
        int hour = c.get(Calendar.HOUR_OF_DAY); // 24小时
        int minute = c.get(Calendar.MINUTE); // 分钟
        if (hour >= 0 && hour < 3) {
            hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC','BOCOM','CMB') ");
        } else if (hour == 3 && minute < 30) {
            hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC','BOCOM','CMB') ");
        } else {
            hql.append(" and bankCode not in ('ICBC','BOC','CCB','ABC','CMB') ");
        }
        
        hql.append("order by totalCheckTimes asc");


        Map<String, Object> params = new HashMap<String, Object>();
        String status =BankOrderStatus.PROCESS;
        params.put("orderStatus", status);
        params.put("date", date);
        params.put("totalCheckTimes", 7);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
    }
    
    private  Date getNextDay(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.add(Calendar.DAY_OF_MONTH, -1);
        date = calendar.getTime();
        return date;
    }
    
    @Override
    public int getCheckOrderCountByBank(Date date,String bankCode) {
        date =getNextDay(date);
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder "); 
        hql.append("where orderStatus = :orderStatus ");
        hql.append(" and sendBankTm >= :date and totalCheckTimes >= :totalCheckTimes "); 
        hql.append(" and totalCheckTimes <12 "); 
        hql.append(" and bankCode=:bankCode ");
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        Map<String, Object> params = new HashMap<String, Object>();
        String status =BankOrderStatus.PROCESS;
        params.put("orderStatus", status);
        params.put("totalCheckTimes", 7);
        params.put("date", date);
        params.put("bankCode", bankCode);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
    }
    
    @Override
    public int getCheckOrderCountByBankExcludeB2b(Date date, String bankCode) {
        date =getNextDay(date);
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder "); 
        hql.append("where orderStatus = :orderStatus ");
        hql.append(" and sendBankTm >= :date and totalCheckTimes >= :totalCheckTimes "); 
        hql.append(" and totalCheckTimes <12 "); 
        hql.append(" and bankCode=:bankCode ");
        hql.append(" and bankBizProdCode not in (:bankBizProdCodeBcdl ,:bankBizProdCodeB2b) ");
        Map<String, Object> params = new HashMap<String, Object>();
        String status =BankOrderStatus.PROCESS;
        params.put("orderStatus", status);
        params.put("totalCheckTimes", 7);
        params.put("date", date);
        params.put("bankCode", bankCode);
        params.put("bankBizProdCodeBcdl", BisBankBusinessProd._09.value);
        params.put("bankBizProdCodeB2b", BisBankBusinessProd._01.value);
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
    }
    
    @Override
    public List<BankOrder> getCheckOrderByBank(Date date, int pageIndex, int pageSize,String bankCode) {
        date =getNextDay(date);
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus =:orderStatus ");
        hql.append(" and sendBankTm >= :date ");    
        hql.append(" and bankBizProdCode != :bankBizProdCode and totalCheckTimes >= :totalCheckTimes ");
        hql.append(" and totalCheckTimes <12 "); 
        hql.append(" and bankCode=:bankCode ");
        hql.append("order by totalCheckTimes asc");


        Map<String, Object> params = new HashMap<String, Object>();
        String status =BankOrderStatus.PROCESS;
        params.put("orderStatus", status);
        params.put("date", date);
        params.put("totalCheckTimes", 7);
        params.put("bankCode", bankCode);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
    }
    @Override
    public List<BankOrder> getCheckOrderByBankExcludeB2b(Date date, int pageIndex, int pageSize, String bankCode) {
        date =getNextDay(date);
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus =:orderStatus ");
        hql.append(" and sendBankTm >= :date ");    
        hql.append(" and bankBizProdCode not in (:bankBizProdCodeBcdl,:bankBizProdCodeCmb) ");
        hql.append(" and totalCheckTimes >= :totalCheckTimes "); 
        hql.append(" and totalCheckTimes <12 "); 
        hql.append(" and bankCode=:bankCode ");
        hql.append("order by totalCheckTimes asc");


        Map<String, Object> params = new HashMap<String, Object>();
        String status =BankOrderStatus.PROCESS;
        params.put("orderStatus", status);
        params.put("date", date);
        params.put("totalCheckTimes", 7);
        params.put("bankCode", bankCode);
        params.put("bankBizProdCodeBcdl", BisBankBusinessProd._09.value);
        params.put("bankBizProdCodeCmb", BisBankBusinessProd._01.value);
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
    }
    
    @Override
    public int getCheckOrderCountByBank(String orderStatus, int count,String bankCode) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrderCount();
        }
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder ");
        hql.append("where orderStatus = :orderStatus and totalCheckTimes = :totalCheckTimes ");
        hql.append(" and bankBizProdCode != :bankBizProdCode ");
        hql.append(" and bankCode=:bankCode ");        
        hql.append(" and orderType = :orderType ");  
        hql.append(buildBeginEndTimeClause());
        
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("totalCheckTimes", count);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("bankCode", bankCode);
            
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
       
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
    }
    
    @Override
    public List<BankOrder> getCheckOrderByBank(String orderStatus, int count, int pageIndex, int pageSize,String bankCode) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrder(pageIndex,  pageSize);
        }
        /*
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus = :orderStatus and totalCheckTimes = :totalCheckTimes ");
        hql.append(" and bankBizProdCode != :bankBizProdCode ");        
        hql.append(" and orderType = :orderType ");   
        hql.append(" and bankCode=:bankCode ");
        hql.append(buildBeginEndTimeClause());
        
        hql.append("order by orderId");
        
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("totalCheckTimes", count);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("bankCode", bankCode);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
       
        
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
        
        */
        
        ////////SQL
        StringBuilder sql = buildQueryReturnFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");       
        if (StringUtils.equals(orderStatus, "92")) {
            sql.append(" and t.TOTAL_CHECK_TIMES <= :totalCheckTimes ");
        } else {
            sql.append(" and t.TOTAL_CHECK_TIMES = :totalCheckTimes ");
        }
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE != :bankBizProdCode ");
        sql.append(" and t.GOPAY_BANK_CODE = :bankCode ");
        sql.append(buildBeginEndTimeClauseOfSQL());
        sql.append(" order by t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
		if (StringUtils.equals(orderStatus, "92")) {
			params.put("totalCheckTimes", 7);
		} else {
			params.put("totalCheckTimes", count);
		}
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);        
        params.put("bankCode", bankCode);
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
        
    }
    @Override
    public List<BankOrder> getCheckOrderByBankExcludeB2b(String orderStatus, int count, int pageIndex, int pageSize,
            String bankCode) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrder(pageIndex,  pageSize);
        }

        StringBuilder sql = buildQueryReturnFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");        
        sql.append(" and t.TOTAL_CHECK_TIMES = :totalCheckTimes ");
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE not in (:bankBizProdCodeBcdl, :bankBizProdCodeB2b ) ");
        sql.append(" and t.GOPAY_BANK_CODE = :bankCode ");
        sql.append(buildBeginEndTimeClauseOfSQL());
        sql.append(" order by t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("totalCheckTimes", count);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCodeBcdl", BisBankBusinessProd._09.value);
        params.put("bankBizProdCodeB2b", BisBankBusinessProd._01.value);
        params.put("bankCode", bankCode);
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
    }
    
    @Override
	public List<BankOrder> getCheckOrderByBankPab(String orderStatus, int totalCheckTimes, int pageIndex,
			int pageSize) {
    	if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrder(pageIndex,  pageSize);
        }
        ////////SQL
        StringBuilder sql = buildQueryReturnFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");        
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE = :bankBizProdCode ");
        sql.append(" and t.GOPAY_BANK_CODE = :bankCode ");
        sql.append(buildBeginEndTimeClauseOfSQLPAB());
        sql.append(" order by t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._15.value);        
        params.put("bankCode", "PAB");
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
        
	}
    
    /** 
     * 查询48小时前 到24小时前 的一天的订单
     * @return
     */
    private String  buildBeginEndTimeClauseOfSQLPAB() {
    	Calendar calendar = Calendar.getInstance();
        //开始时间--最近两天
        calendar.setTime(new Date());
        calendar.add(Calendar.DAY_OF_MONTH, -2);
        String beginTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        
        //截至时间--最近一天
        calendar.setTime(new Date());
        calendar.add(Calendar.DAY_OF_MONTH, -1);
        String endTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        
        StringBuilder sb=new StringBuilder(" and t.SEND_BANK_TM <to_date('"+endTime+"','yyyyMMddHH24miss')");
        sb.append(" and t.SEND_BANK_TM >to_date('"+beginTime+"','yyyyMMddHH24miss')");
        return  sb.toString();
	}
    
    private String buildBCDLTimeClause(){
        Calendar calendar = Calendar.getInstance();
        
        //开始时间--最近1天
        calendar.setTime(new Date());
        calendar.add(Calendar.DAY_OF_MONTH, -1);
        String beginTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        //结束时间--当前时间5分钟前
        calendar = Calendar.getInstance();
        calendar.add(Calendar.MINUTE, -5);
        String endTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        
        StringBuilder sb=new StringBuilder();
        sb.append(" and sendBankTm >to_date('"+beginTime+"','yyyyMMddHH24miss') ");
        sb.append(" and sendBankTm <=to_date('"+endTime+"','yyyyMMddHH24miss') ");
        
        return sb.toString();
    }
    
    public int getCheckBcdlOrderCountByBank(String bank,String orderStatus){
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder ");
        hql.append("where orderStatus = :orderStatus and totalCheckTimes <= :totalCheckTimes ");
        hql.append("and bankBizProdCode = :bankBizProdCode and bankCode=:bankCode ");
        // 获取订单增加渠道码条件
        hql.append("and interfaceChennelCode = :interfaceChennelCode ");
        hql.append("and orderType = :orderType ");      
        hql.append(buildBCDLTimeClause());

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("totalCheckTimes", 7);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("interfaceChennelCode", BisBankInterfaceChannel._00.value);
        params.put("bankCode", bank);
        
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
        
    }
    
    public int getCheckBcdlOrderCountByBankLarge(String bank,String orderStatus){
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder ");
        hql.append("where orderStatus = :orderStatus and totalCheckTimes <= :totalCheckTimes ");
        hql.append("and bankBizProdCode = :bankBizProdCode and bankCode=:bankCode ");
        // 获取订单增加渠道码条件
        hql.append("and interfaceChennelCode = :interfaceChennelCode ");
        hql.append("and orderType = :orderType ");      
        hql.append(buildBCDLTimeClause());

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("totalCheckTimes", 15);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("interfaceChennelCode", BisBankInterfaceChannel._00.value);
        params.put("bankCode", bank);
        
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
        
    }
    
    public List<BankOrder> getCheckBcdlOrderByBank(String bank,String orderStatus, int pageIndex, int pageSize){
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus = :orderStatus   ");
        hql.append("and bankBizProdCode = :bankBizProdCode ");
        // 获取订单增加渠道码条件
        hql.append("and interfaceChennelCode = :interfaceChennelCode ");
        hql.append("and orderType = :orderType and bankCode=:bankCode and  totalCheckTimes <= :totalCheckTimes ");  
        hql.append(buildBCDLTimeClause());
        
        //修改T+1插队到T+0问题，改成订单号降序 edit by fanghw 20160214
        hql.append(" order by totalCheckTimes,orderId desc ");

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("interfaceChennelCode", BisBankInterfaceChannel._00.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("bankCode", bank);
        params.put("totalCheckTimes", 7);
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
    }
   
    
    public List<BankOrder> getCheckBcdlOrderByBanklarge(String bank,String orderStatus, int pageIndex, int pageSize){
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus = :orderStatus   ");
        hql.append("and bankBizProdCode = :bankBizProdCode ");
        // 获取订单增加渠道码条件
        hql.append("and interfaceChennelCode = :interfaceChennelCode ");
        hql.append("and orderType = :orderType and bankCode=:bankCode and  totalCheckTimes <= :totalCheckTimes ");  
        hql.append(buildBCDLTimeClause());
        
        //修改T+1插队到T+0问题，改成订单号降序 edit by fanghw 20160214
        hql.append(" order by totalCheckTimes,orderId desc ");

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("interfaceChennelCode", BisBankInterfaceChannel._00.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("bankCode", bank);
        params.put("totalCheckTimes", 15);
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
    }
    
    private StringBuilder buildQueryReturnFields(){
        StringBuilder sql = new StringBuilder("select /*+ INDEX(t IDX_BANK_STATUS_TM) */ ");
        sql.append(" t.GOPAY_OUT_ORDER_ID, ");
        sql.append(" t.GOPAY_BANK_CODE ");
        sql.append(" from BIS_BANK_MAIN_ORDER t ");
        
        return sql;
    }
    
    /** 
     * 从小额免密视图中查询数据 
     * @return 
     * @author w_jiqing 
     */
    private StringBuilder buildQueryReturnMicroFields(){
        StringBuilder sql = new StringBuilder("select  ");
        sql.append(" t.GOPAY_OUT_ORDER_ID, ");
        sql.append(" t.GOPAY_BANK_CODE ");
        sql.append(" from BIS_BANK_MAIN_ORDER_MICRO_VIEW t ");
        
        return sql;
        
    }
    
    private String buildBeginEndTimeClauseOfSQL(){
        Calendar calendar = Calendar.getInstance();
        
        //开始时间--最近一天
        calendar.setTime(new Date());
        calendar.add(Calendar.DAY_OF_MONTH, -1);
        String beginTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        
        //截至时间--90秒前
        calendar.setTime(new Date());
        calendar.add(Calendar.SECOND, -90);
        String endTime=DateUtils.format(calendar.getTime(), "yyyyMMddHHmmss");
        
        StringBuilder sb=new StringBuilder(" and t.SEND_BANK_TM <to_date('"+endTime+"','yyyyMMddHH24miss')");
        sb.append(" and t.SEND_BANK_TM >to_date('"+beginTime+"','yyyyMMddHH24miss')");
        
        
        return sb.toString();
    }
    

    
    private List<BankOrder> buildQueryResult(List<Object[]> rsList){
        List<BankOrder> orderList=new ArrayList<BankOrder>();

        String orderId;
        for (Object[] arr : rsList) {
            orderId=(String) arr[0];
            if(StringUtils.isNotBlank(orderId)){
                orderList.add(this.find(BankOrder.class, orderId));
            }
        }
        
        return orderList;
    }
    
    protected List<Object[]> findBySqlOfCustom(final String sql, final Map<String, Object> param, final int pageNo, final int pageSize) {
        return (List<Object[]>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(sql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                            query.setParameter(key, param.get(key));
                        }
                    }
                }
                if (pageNo >= 1) {
                    query.setFirstResult((pageNo - 1) * pageSize);
                    query.setMaxResults(pageSize);
                }
                List<Object[]> list = query.list();
                return list;
            }
        });
    }
    
    /**
     * 根据SQL语句查询记录总数之Custom版
     * 
     * @param sql
     * @param param
     * @return
     */
    public int findCountBySqlOfCustom(final String sql, final Map<String, Object> param) {
        return (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(sql);
                if (param != null) {
                    Iterator<String> it = param.keySet().iterator();
                    while (it.hasNext()) {
                        String key = it.next();
                        Object value = param.get(key);
                        if (value instanceof Object[]) {
                            query.setParameterList(key, (Object[]) value);
                        } else {
//                            query.setString(key, param.get(key).toString());
                            query.setParameter(key, param.get(key));
                        }
                    }
                }
                List<Object[]> list = query.list();
                return Integer.parseInt(list.get(0).toString());
            }
        });
    }

    @Override
    public List<BankOrder> getCheckOrderByBankProd(String orderStatus, int count, int pageIndex, int pageSize,
            String bankProd) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrder(pageIndex,  pageSize);
        }        
        ////////SQL
        StringBuilder sql = buildQueryReturnFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");        
        sql.append(" and t.TOTAL_CHECK_TIMES = :totalCheckTimes ");
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE = :bankBizProdCode ");
        sql.append(buildBeginEndTimeClauseOfSQL());
        sql.append(" order by t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("totalCheckTimes", count);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", bankProd);        
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
        
    }

    @Override
    public List<BankOrder> getCheckOrderViewByBankProd(String orderStatus, int count, int pageIndex, int pageSize,
            String bankProd) {
        if(BankOrderStatus.BANK_CALL_SUCCESS.equals(orderStatus)){
            return getBankCallSuCheckOrder(pageIndex,  pageSize);
        }        
        ////////SQL
        StringBuilder sql = buildQueryReturnMicroFields();
        sql.append(" where 1=1 ");
        sql.append(" and t.ORDER_TYPE = :orderType ");        
        sql.append(" and t.TOTAL_CHECK_TIMES = :totalCheckTimes ");
        sql.append(" and t.ORDER_STATUS = :orderStatus ");
        sql.append(" and t.BANK_BIZ_PROD_CODE = :bankBizProdCode ");
        //sql.append(buildBeginEndTimeClauseOfSQL());
        sql.append(" order by t.GOPAY_ORDER_ID asc ");
        
        //param
        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("totalCheckTimes", count);
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", bankProd);        
        
        List<Object[]> rsList=findBySqlOfCustom(sql.toString(), params, pageIndex, pageSize);
        
        return buildQueryResult(rsList);
    }

    @Override
    public int getCheckBcdlOrderCountByChannel(String channel, String orderStatus) {
        StringBuffer hql = new StringBuffer("");
        hql.append("select count(*) from BankOrder ");
        hql.append("where orderStatus = :orderStatus and totalCheckTimes <= :totalCheckTimes ");
        hql.append("and bankBizProdCode = :bankBizProdCode and interfaceChennelCode=:interfaceChennelCode ");
        hql.append("and orderType = :orderType ");      
        hql.append(buildBCDLTimeClause());

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("totalCheckTimes", 7);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("interfaceChennelCode", channel);
        
        int rsCount = this.findCountByHql(hql.toString(), params);

        return rsCount;
        
    }

    @Override
    public List<BankOrder> getCheckBcdlOrderByChannel(String channel, String orderStatus, int pageIndex, int pageSize) {
        StringBuffer hql = new StringBuffer("");
        hql.append("from BankOrder ");
        hql.append("where orderStatus = :orderStatus   ");
        hql.append("and bankBizProdCode = :bankBizProdCode ");
        hql.append("and orderType = :orderType and interfaceChennelCode=:interfaceChennelCode and  totalCheckTimes <= :totalCheckTimes ");  
        hql.append(buildBCDLTimeClause());
        
        //修改T+1插队到T+0问题，改成订单号降序 edit by fanghw 20160214
        hql.append(" order by totalCheckTimes,orderId desc ");

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("orderStatus", orderStatus);
        params.put("bankBizProdCode", BisBankBusinessProd._09.value);
        params.put("orderType", BankOrderType.GOPAY_BIS_ORDER);
        params.put("interfaceChennelCode", channel);
        params.put("totalCheckTimes", 7);
        List<BankOrder> list = this.findByHql(hql.toString(), params, pageIndex, pageSize);

        return list;
    }

}
